<?php
/**
 * Zend Framework
 * LICENSE
 * This source file is subject to the new BSD license that is bundled
 * with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://framework.zend.com/license/new-bsd
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@zend.com so we can send you a copy immediately.
 * @category Zend
 * @package Zend_Db
 * @subpackage Select
 * @copyright Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)
 * @license http://framework.zend.com/license/new-bsd New BSD License
 * @version $Id: Select.php 24833 2012-05-30 13:29:41Z adamlundrigan $
 */
/**
 *
 * @see Zend_Db_Adapter_Abstract
 */
require_once 'Zend/Db/Adapter/Abstract.php';
/**
 *
 * @see Zend_Db_Expr
 */
require_once 'Zend/Db/Expr.php';

/**
 * Class for SQL SELECT generation and results.
 * @category Zend
 * @package Zend_Db
 * @subpackage Select
 * @copyright Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)
 * @license http://framework.zend.com/license/new-bsd New BSD License
 */
class Zend_Db_Select {

    const DISTINCT = 'distinct';

    const COLUMNS = 'columns';

    const FROM = 'from';

    const UNION = 'union';

    const WHERE = 'where';

    const GROUP = 'group';

    const HAVING = 'having';

    const ORDER = 'order';

    const LIMIT_COUNT = 'limitcount';

    const LIMIT_OFFSET = 'limitoffset';

    const FOR_UPDATE = 'forupdate';

    const INNER_JOIN = 'inner join';

    const LEFT_JOIN = 'left join';

    const RIGHT_JOIN = 'right join';

    const FULL_JOIN = 'full join';

    const CROSS_JOIN = 'cross join';

    const NATURAL_JOIN = 'natural join';

    const SQL_WILDCARD = '*';

    const SQL_SELECT = 'SELECT';

    const SQL_UNION = 'UNION';

    const SQL_UNION_ALL = 'UNION ALL';

    const SQL_FROM = 'FROM';

    const SQL_WHERE = 'WHERE';

    const SQL_DISTINCT = 'DISTINCT';

    const SQL_GROUP_BY = 'GROUP BY';

    const SQL_ORDER_BY = 'ORDER BY';

    const SQL_HAVING = 'HAVING';

    const SQL_FOR_UPDATE = 'FOR UPDATE';

    const SQL_AND = 'AND';

    const SQL_AS = 'AS';

    const SQL_OR = 'OR';

    const SQL_ON = 'ON';

    const SQL_ASC = 'ASC';

    const SQL_DESC = 'DESC';

    /**
     * Bind variables for query
     * @var array
     */
    protected $_bind = array();

    /**
     * Zend_Db_Adapter_Abstract object.
     * @var Zend_Db_Adapter_Abstract
     */
    protected $_adapter;

    /**
     * The initial values for the $_parts array.
     * NOTE: It is important for the 'FOR_UPDATE' part to be last to ensure
     * meximum compatibility with database adapters.
     * @var array
     */
    protected static $_partsInit = array(self::DISTINCT => false, self::COLUMNS => array(), self::UNION => array(), self::FROM => array(), self::WHERE => array(), self::GROUP => array(), self::HAVING => array(), self::ORDER => array(), self::LIMIT_COUNT => null, self::LIMIT_OFFSET => null, self::FOR_UPDATE => false);

    /**
     * Specify legal join types.
     * @var array
     */
    protected static $_joinTypes = array(self::INNER_JOIN, self::LEFT_JOIN, self::RIGHT_JOIN, self::FULL_JOIN, self::CROSS_JOIN, self::NATURAL_JOIN);

    /**
     * Specify legal union types.
     * @var array
     */
    protected static $_unionTypes = array(self::SQL_UNION, self::SQL_UNION_ALL);

    /**
     * The component parts of a SELECT statement.
     * Initialized to the $_partsInit array in the constructor.
     * @var array
     */
    protected $_parts = array();

    /**
     * Tracks which columns are being select from each table and join.
     * @var array
     */
    protected $_tableCols = array();

    /**
     * Class constructor
     * @param Zend_Db_Adapter_Abstract $adapter
     */
    public function __construct (Zend_Db_Adapter_Abstract $adapter) {
        $this -> _adapter = $adapter;
        $this -> _parts = self::$_partsInit;
    }

    /**
     * Get bind variables
     * @return array
     */
    public function getBind () {
        return $this -> _bind;
    }

    /**
     * Set bind variables
     * @param mixed $bind
     * @return Zend_Db_Select
     */
    public function bind ($bind) {
        $this -> _bind = $bind;
        return $this;
    }

    /**
     * Makes the query SELECT DISTINCT.
     * @param bool $flag Whether or not the SELECT is DISTINCT (default true).
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function distinct ($flag = true) {
        $this -> _parts[self::DISTINCT] = (bool) $flag;
        return $this;
    }

    /**
     * Adds a FROM table and optional columns to the query.
     * The first parameter $name can be a simple string, in which case the
     * correlation name is generated automatically. If you want to specify
     * the correlation name, the first parameter must be an associative
     * array in which the key is the correlation name, and the value is
     * the physical table name. For example, array('alias' => 'table').
     * The correlation name is prepended to all columns fetched for this
     * table.
     * The second parameter can be a single string or Zend_Db_Expr object,
     * or else an array of strings or Zend_Db_Expr objects.
     * The first parameter can be null or an empty string, in which case
     * no correlation name is generated or prepended to the columns named
     * in the second parameter.
     * @param array|string|Zend_Db_Expr $name The table name or an associative array
     *        relating correlation name to table name.
     * @param array|string|Zend_Db_Expr $cols The columns to select from this table.
     * @param string $schema The schema name to specify, if any.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function from ($name, $cols = '*', $schema = null) {
        return $this -> _join (self::FROM, $name, null, $cols, $schema);
    }

    /**
     * Specifies the columns used in the FROM clause.
     * The parameter can be a single string or Zend_Db_Expr object,
     * or else an array of strings or Zend_Db_Expr objects.
     * @param array|string|Zend_Db_Expr $cols The columns to select from this table.
     * @param string $correlationName Correlation name of target table. OPTIONAL
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function columns ($cols = '*', $correlationName = null) {
        if ($correlationName === null && count ($this -> _parts[self::FROM])) {
            $correlationNameKeys = array_keys ($this -> _parts[self::FROM]);
            $correlationName = current ($correlationNameKeys);
        }
        if ( ! array_key_exists ($correlationName, $this -> _parts[self::FROM])) {
            /**
             *
             * @see Zend_Db_Select_Exception
             */
            require_once 'Zend/Db/Select/Exception.php';
            throw new Zend_Db_Select_Exception ("No table has been specified for the FROM clause");
        }
        $this -> _tableCols ($correlationName, $cols);
        return $this;
    }

    /**
     * Adds a UNION clause to the query.
     * The first parameter has to be an array of Zend_Db_Select or
     * sql query strings.
     * <code>
     * $sql1 = $db->select();
     * $sql2 = "SELECT ...";
     * $select = $db->select()
     * ->union(array($sql1, $sql2))
     * ->order("id");
     * </code>
     * @param array $select Array of select clauses for the union.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function union ($select = array(), $type = self::SQL_UNION) {
        if ( ! is_array ($select)) {
            require_once 'Zend/Db/Select/Exception.php';
            throw new Zend_Db_Select_Exception ("union() only accepts an array of Zend_Db_Select instances of sql query strings.");
        }
        if ( ! in_array ($type, self::$_unionTypes)) {
            require_once 'Zend/Db/Select/Exception.php';
            throw new Zend_Db_Select_Exception ("Invalid union type '{$type}'");
        }
        foreach ($select as $target) {
            $this -> _parts[self::UNION][] = array($target, $type);
        }
        return $this;
    }

    /**
     * Adds a JOIN table and columns to the query.
     * The $name and $cols parameters follow the same logic
     * as described in the from() method.
     * @param array|string|Zend_Db_Expr $name The table name.
     * @param string $cond Join on this condition.
     * @param array|string $cols The columns to select from the joined table.
     * @param string $schema The database name to specify, if any.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function join ($name, $cond, $cols = self::SQL_WILDCARD, $schema = null) {
        return $this -> joinInner ($name, $cond, $cols, $schema);
    }

    /**
     * Add an INNER JOIN table and colums to the query
     * Rows in both tables are matched according to the expression
     * in the $cond argument.
     * The result set is comprised
     * of all cases where rows from the left table match
     * rows from the right table.
     * The $name and $cols parameters follow the same logic
     * as described in the from() method.
     * @param array|string|Zend_Db_Expr $name The table name.
     * @param string $cond Join on this condition.
     * @param array|string $cols The columns to select from the joined table.
     * @param string $schema The database name to specify, if any.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function joinInner ($name, $cond, $cols = self::SQL_WILDCARD, $schema = null) {
        return $this -> _join (self::INNER_JOIN, $name, $cond, $cols, $schema);
    }

    /**
     * Add a LEFT OUTER JOIN table and colums to the query
     * All rows from the left operand table are included,
     * matching rows from the right operand table included,
     * and the columns from the right operand table are filled
     * with NULLs if no row exists matching the left table.
     * The $name and $cols parameters follow the same logic
     * as described in the from() method.
     * @param array|string|Zend_Db_Expr $name The table name.
     * @param string $cond Join on this condition.
     * @param array|string $cols The columns to select from the joined table.
     * @param string $schema The database name to specify, if any.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function joinLeft ($name, $cond, $cols = self::SQL_WILDCARD, $schema = null) {
        return $this -> _join (self::LEFT_JOIN, $name, $cond, $cols, $schema);
    }

    /**
     * Add a RIGHT OUTER JOIN table and colums to the query.
     * Right outer join is the complement of left outer join.
     * All rows from the right operand table are included,
     * matching rows from the left operand table included,
     * and the columns from the left operand table are filled
     * with NULLs if no row exists matching the right table.
     * The $name and $cols parameters follow the same logic
     * as described in the from() method.
     * @param array|string|Zend_Db_Expr $name The table name.
     * @param string $cond Join on this condition.
     * @param array|string $cols The columns to select from the joined table.
     * @param string $schema The database name to specify, if any.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function joinRight ($name, $cond, $cols = self::SQL_WILDCARD, $schema = null) {
        return $this -> _join (self::RIGHT_JOIN, $name, $cond, $cols, $schema);
    }

    /**
     * Add a FULL OUTER JOIN table and colums to the query.
     * A full outer join is like combining a left outer join
     * and a right outer join. All rows from both tables are
     * included, paired with each other on the same row of the
     * result set if they satisfy the join condition, and otherwise
     * paired with NULLs in place of columns from the other table.
     * The $name and $cols parameters follow the same logic
     * as described in the from() method.
     * @param array|string|Zend_Db_Expr $name The table name.
     * @param string $cond Join on this condition.
     * @param array|string $cols The columns to select from the joined table.
     * @param string $schema The database name to specify, if any.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function joinFull ($name, $cond, $cols = self::SQL_WILDCARD, $schema = null) {
        return $this -> _join (self::FULL_JOIN, $name, $cond, $cols, $schema);
    }

    /**
     * Add a CROSS JOIN table and colums to the query.
     * A cross join is a cartesian product; there is no join condition.
     * The $name and $cols parameters follow the same logic
     * as described in the from() method.
     * @param array|string|Zend_Db_Expr $name The table name.
     * @param array|string $cols The columns to select from the joined table.
     * @param string $schema The database name to specify, if any.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function joinCross ($name, $cols = self::SQL_WILDCARD, $schema = null) {
        return $this -> _join (self::CROSS_JOIN, $name, null, $cols, $schema);
    }

    /**
     * Add a NATURAL JOIN table and colums to the query.
     * A natural join assumes an equi-join across any column(s)
     * that appear with the same name in both tables.
     * Only natural inner joins are supported by this API,
     * even though SQL permits natural outer joins as well.
     * The $name and $cols parameters follow the same logic
     * as described in the from() method.
     * @param array|string|Zend_Db_Expr $name The table name.
     * @param array|string $cols The columns to select from the joined table.
     * @param string $schema The database name to specify, if any.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function joinNatural ($name, $cols = self::SQL_WILDCARD, $schema = null) {
        return $this -> _join (self::NATURAL_JOIN, $name, null, $cols, $schema);
    }

    /**
     * Adds a WHERE condition to the query by AND.
     * If a value is passed as the second param, it will be quoted
     * and replaced into the condition wherever a question-mark
     * appears. Array values are quoted and comma-separated.
     * <code>
     * // simplest but non-secure
     * $select->where("id = $id");
     * // secure (ID is quoted but matched anyway)
     * $select->where('id = ?', $id);
     * // alternatively, with named binding
     * $select->where('id = :id');
     * </code>
     * Note that it is more correct to use named bindings in your
     * queries for values other than strings. When you use named
     * bindings, don't forget to pass the values when actually
     * making a query:
     * <code>
     * $db->fetchAll($select, array('id' => 5));
     * </code>
     * @param string $cond The WHERE condition.
     * @param mixed $value OPTIONAL The value to quote into the condition.
     * @param int $type OPTIONAL The type of the given value
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function where ($cond, $value = null, $type = null) {
        $this -> _parts[self::WHERE][] = $this -> _where ($cond, $value, $type, true);
        return $this;
    }

    /**
     * Adds a WHERE condition to the query by OR.
     * Otherwise identical to where().
     * @param string $cond The WHERE condition.
     * @param mixed $value OPTIONAL The value to quote into the condition.
     * @param int $type OPTIONAL The type of the given value
     * @return Zend_Db_Select This Zend_Db_Select object.
     * @see where()
     */
    public function orWhere ($cond, $value = null, $type = null) {
        $this -> _parts[self::WHERE][] = $this -> _where ($cond, $value, $type, false);
        return $this;
    }

    /**
     * Adds grouping to the query.
     * @param array|string $spec The column(s) to group by.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function group ($spec) {
        if ( ! is_array ($spec)) {
            $spec = array($spec);
        }
        foreach ($spec as $val) {
            if (preg_match ('/\(.*\)/', (string) $val)) {
                $val = new Zend_Db_Expr ($val);
            }
            $this -> _parts[self::GROUP][] = $val;
        }
        return $this;
    }

    /**
     * Adds a HAVING condition to the query by AND.
     * If a value is passed as the second param, it will be quoted
     * and replaced into the condition wherever a question-mark
     * appears. See {@link where()} for an example
     * @param string $cond The HAVING condition.
     * @param mixed $value OPTIONAL The value to quote into the condition.
     * @param int $type OPTIONAL The type of the given value
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function having ($cond, $value = null, $type = null) {
        if ($value !== null) {
            $cond = $this -> _adapter -> quoteInto ($cond, $value, $type);
        }
        if ($this -> _parts[self::HAVING]) {
            $this -> _parts[self::HAVING][] = self::SQL_AND . " ($cond)";
        } else {
            $this -> _parts[self::HAVING][] = "($cond)";
        }
        return $this;
    }

    /**
     * Adds a HAVING condition to the query by OR.
     * Otherwise identical to orHaving().
     * @param string $cond The HAVING condition.
     * @param mixed $value OPTIONAL The value to quote into the condition.
     * @param int $type OPTIONAL The type of the given value
     * @return Zend_Db_Select This Zend_Db_Select object.
     * @see having()
     */
    public function orHaving ($cond, $value = null, $type = null) {
        if ($value !== null) {
            $cond = $this -> _adapter -> quoteInto ($cond, $value, $type);
        }
        if ($this -> _parts[self::HAVING]) {
            $this -> _parts[self::HAVING][] = self::SQL_OR . " ($cond)";
        } else {
            $this -> _parts[self::HAVING][] = "($cond)";
        }
        return $this;
    }

    /**
     * Adds a row order to the query.
     * @param mixed $spec The column(s) and direction to order by.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function order ($spec) {
        if ( ! is_array ($spec)) {
            $spec = array($spec);
        }
        // force 'ASC' or 'DESC' on each order spec, default is ASC.
        foreach ($spec as $val) {
            if ($val instanceof Zend_Db_Expr) {
                $expr = $val -> __toString ();
                if (empty ($expr)) {
                    continue;
                }
                $this -> _parts[self::ORDER][] = $val;
            } else {
                if (empty ($val)) {
                    continue;
                }
                $direction = self::SQL_ASC;
                if (preg_match ('/(.*\W)(' . self::SQL_ASC . '|' . self::SQL_DESC . ')\b/si', $val, $matches)) {
                    $val = trim ($matches[1]);
                    $direction = $matches[2];
                }
                if (preg_match ('/\(.*\)/', $val)) {
                    $val = new Zend_Db_Expr ($val);
                }
                $this -> _parts[self::ORDER][] = array($val, $direction);
            }
        }
        return $this;
    }

    /**
     * Sets a limit count and offset to the query.
     * @param int $count OPTIONAL The number of rows to return.
     * @param int $offset OPTIONAL Start returning after this many rows.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function limit ($count = null, $offset = null) {
        $this -> _parts[self::LIMIT_COUNT] = (int) $count;
        $this -> _parts[self::LIMIT_OFFSET] = (int) $offset;
        return $this;
    }

    /**
     * Sets the limit and count by page number.
     * @param int $page Limit results to this page number.
     * @param int $rowCount Use this many rows per page.
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function limitPage ($page, $rowCount) {
        $page = ($page > 0) ? $page : 1;
        $rowCount = ($rowCount > 0) ? $rowCount : 1;
        $this -> _parts[self::LIMIT_COUNT] = (int) $rowCount;
        $this -> _parts[self::LIMIT_OFFSET] = (int) $rowCount * ($page - 1);
        return $this;
    }

    /**
     * Makes the query SELECT FOR UPDATE.
     * @param bool $flag Whether or not the SELECT is FOR UPDATE (default true).
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function forUpdate ($flag = true) {
        $this -> _parts[self::FOR_UPDATE] = (bool) $flag;
        return $this;
    }

    /**
     * Get part of the structured information for the currect query.
     * @param string $part
     * @return mixed
     * @throws Zend_Db_Select_Exception
     */
    public function getPart ($part) {
        $part = strtolower ($part);
        if ( ! array_key_exists ($part, $this -> _parts)) {
            require_once 'Zend/Db/Select/Exception.php';
            throw new Zend_Db_Select_Exception ("Invalid Select part '$part'");
        }
        return $this -> _parts[$part];
    }

    /**
     * Executes the current select object and returns the result
     * @param integer $fetchMode OPTIONAL
     * @param mixed $bind An array of data to bind to the placeholders.
     * @return PDO_Statement Zend_Db_Statement
     */
    public function query ($fetchMode = null, $bind = array()) {
        if ( ! empty ($bind)) {
            $this -> bind ($bind);
        }
        $stmt = $this -> _adapter -> query ($this);
        if ($fetchMode == null) {
            $fetchMode = $this -> _adapter -> getFetchMode ();
        }
        $stmt -> setFetchMode ($fetchMode);
        return $stmt;
    }

    /**
     * Converts this object to an SQL SELECT string.
     * @return string null object as a SELECT string. (or null if a string cannot be produced.)
     */
    public function assemble () {
        $sql = self::SQL_SELECT;
        foreach (array_keys (self::$_partsInit) as $part) {
            $method = '_render' . ucfirst ($part);
            if (method_exists ($this, $method)) {
                $sql = $this -> $method ($sql);
            }
        }
        return $sql;
    }

    /**
     * Clear parts of the Select object, or an individual part.
     * @param string $part OPTIONAL
     * @return Zend_Db_Select
     */
    public function reset ($part = null) {
        if ($part == null) {
            $this -> _parts = self::$_partsInit;
        } else 
            if (array_key_exists ($part, self::$_partsInit)) {
                $this -> _parts[$part] = self::$_partsInit[$part];
            }
        return $this;
    }

    /**
     * Gets the Zend_Db_Adapter_Abstract for this
     * particular Zend_Db_Select object.
     * @return Zend_Db_Adapter_Abstract
     */
    public function getAdapter () {
        return $this -> _adapter;
    }

    /**
     * Populate the {@link $_parts} 'join' key
     * Does the dirty work of populating the join key.
     * The $name and $cols parameters follow the same logic
     * as described in the from() method.
     * @param null|string $type Type of join; inner, left, and null are currently supported
     * @param array|string|Zend_Db_Expr $name Table name
     * @param string $cond Join on this condition
     * @param array|string $cols The columns to select from the joined table
     * @param string $schema The database name to specify, if any.
     * @return Zend_Db_Select This Zend_Db_Select object
     * @throws Zend_Db_Select_Exception
     */
    protected function _join ($type, $name, $cond, $cols, $schema = null) {
        if ( ! in_array ($type, self::$_joinTypes) && $type != self::FROM) {
            /**
             *
             * @see Zend_Db_Select_Exception
             */
            require_once 'Zend/Db/Select/Exception.php';
            throw new Zend_Db_Select_Exception ("Invalid join type '$type'");
        }
        if (count ($this -> _parts[self::UNION])) {
            require_once 'Zend/Db/Select/Exception.php';
            throw new Zend_Db_Select_Exception ("Invalid use of table with " . self::SQL_UNION);
        }
        if (empty ($name)) {
            $correlationName = $tableName = '';
        } else 
            if (is_array ($name)) {
                // Must be array($correlationName => $tableName) or array($ident, ...)
                foreach ($name as $_correlationName => $_tableName) {
                    if (is_string ($_correlationName)) {
                        // We assume the key is the correlation name and value is the table name
                        $tableName = $_tableName;
                        $correlationName = $_correlationName;
                    } else {
                        // We assume just an array of identifiers, with no correlation name
                        $tableName = $_tableName;
                        $correlationName = $this -> _uniqueCorrelation ($tableName);
                    }
                    break;
                }
            } else 
                if ($name instanceof Zend_Db_Expr || $name instanceof Zend_Db_Select) {
                    $tableName = $name;
                    $correlationName = $this -> _uniqueCorrelation ('t');
                } else 
                    if (preg_match ('/^(.+)\s+AS\s+(.+)$/i', $name, $m)) {
                        $tableName = $m[1];
                        $correlationName = $m[2];
                    } else {
                        $tableName = $name;
                        $correlationName = $this -> _uniqueCorrelation ($tableName);
                    }
        // Schema from table name overrides schema argument
        if ( ! is_object ($tableName) && false !== strpos ($tableName, '.')) {
            list ($schema, $tableName) = explode ('.', $tableName);
        }
        $lastFromCorrelationName = null;
        if ( ! empty ($correlationName)) {
            if (array_key_exists ($correlationName, $this -> _parts[self::FROM])) {
                /**
                 *
                 * @see Zend_Db_Select_Exception
                 */
                require_once 'Zend/Db/Select/Exception.php';
                throw new Zend_Db_Select_Exception ("You cannot define a correlation name '$correlationName' more than once");
            }
            if ($type == self::FROM) {
                // append this from after the last from joinType
                $tmpFromParts = $this -> _parts[self::FROM];
                $this -> _parts[self::FROM] = array();
                // move all the froms onto the stack
                while ($tmpFromParts) {
                    $currentCorrelationName = key ($tmpFromParts);
                    if ($tmpFromParts[$currentCorrelationName]['joinType'] != self::FROM) {
                        break;
                    }
                    $lastFromCorrelationName = $currentCorrelationName;
                    $this -> _parts[self::FROM][$currentCorrelationName] = array_shift ($tmpFromParts);
                }
            } else {
                $tmpFromParts = array();
            }
            $this -> _parts[self::FROM][$correlationName] = array('joinType' => $type, 'schema' => $schema, 'tableName' => $tableName, 'joinCondition' => $cond);
            while ($tmpFromParts) {
                $currentCorrelationName = key ($tmpFromParts);
                $this -> _parts[self::FROM][$currentCorrelationName] = array_shift ($tmpFromParts);
            }
        }
        // add to the columns from this joined table
        if ($type == self::FROM && $lastFromCorrelationName == null) {
            $lastFromCorrelationName = true;
        }
        $this -> _tableCols ($correlationName, $cols, $lastFromCorrelationName);
        return $this;
    }

    /**
     * Handle JOIN...
     * USING... syntax
     * This is functionality identical to the existing JOIN methods, however
     * the join condition can be passed as a single column name. This method
     * then completes the ON condition by using the same field for the FROM
     * table and the JOIN table.
     * <code>
     * $select = $db->select()->from('table1')
     * ->joinUsing('table2', 'column1');
     * // SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2
     * </code>
     * These joins are called by the developer simply by adding 'Using' to the
     * method name. E.g.
     * * joinUsing
     * * joinInnerUsing
     * * joinFullUsing
     * * joinRightUsing
     * * joinLeftUsing
     * @return Zend_Db_Select This Zend_Db_Select object.
     */
    public function _joinUsing ($type, $name, $cond, $cols = '*', $schema = null) {
        if (empty ($this -> _parts[self::FROM])) {
            require_once 'Zend/Db/Select/Exception.php';
            throw new Zend_Db_Select_Exception ("You can only perform a joinUsing after specifying a FROM table");
        }
        $join = $this -> _adapter -> quoteIdentifier (key ($this -> _parts[self::FROM]), true);
        $from = $this -> _adapter -> quoteIdentifier ($this -> _uniqueCorrelation ($name), true);
        $joinCond = array();
        foreach ((array) $cond as $fieldName) {
            $cond1 = $from . '.' . $fieldName;
            $cond2 = $join . '.' . $fieldName;
            $joinCond[] = $cond1 . ' = ' . $cond2;
        }
        $cond = implode (' ' . self::SQL_AND . ' ', $joinCond);
        return $this -> _join ($type, $name, $cond, $cols, $schema);
    }

    /**
     * Generate a unique correlation name
     * @param string|array $name A qualified identifier.
     * @return string A unique correlation name.
     */
    private function _uniqueCorrelation ($name) {
        if (is_array ($name)) {
            $k = key ($name);
            $c = is_string ($k) ? $k : end ($name);
        } else {
            // Extract just the last name of a qualified table name
            $dot = strrpos ($name, '.');
            $c = ($dot === false) ? $name : substr ($name, $dot + 1);
        }
        for ($i = 2; array_key_exists ($c, $this -> _parts[self::FROM]);  ++ $i) {
            $c = $name . '_' . (string) $i;
        }
        return $c;
    }

    /**
     * Adds to the internal table-to-column mapping array.
     * @param string $tbl The table/join the columns come from.
     * @param array|string $cols The list of columns; preferably as
     *        an array, but possibly as a string containing one column.
     * @param bool|string True if it should be prepended, a correlation name if it should be inserted
     * @return void
     */
    protected function _tableCols ($correlationName, $cols, $afterCorrelationName = null) {
        if ( ! is_array ($cols)) {
            $cols = array($cols);
        }
        if ($correlationName == null) {
            $correlationName = '';
        }
        $columnValues = array();
        foreach (array_filter ($cols) as $alias => $col) {
            $currentCorrelationName = $correlationName;
            if (is_string ($col)) {
                // Check for a column matching "<column> AS <alias>" and extract the alias name
                if (preg_match ('/^(.+)\s+' . self::SQL_AS . '\s+(.+)$/i', $col, $m)) {
                    $col = $m[1];
                    $alias = $m[2];
                }
                // Check for columns that look like functions and convert to Zend_Db_Expr
                if (preg_match ('/\(.*\)/', $col)) {
                    $col = new Zend_Db_Expr ($col);
                } elseif (preg_match ('/(.+)\.(.+)/', $col, $m)) {
                    $currentCorrelationName = $m[1];
                    $col = $m[2];
                }
            }
            $columnValues[] = array($currentCorrelationName, $col, is_string ($alias) ? $alias : null);
        }
        if ($columnValues) {
            // should we attempt to prepend or insert these values?
            if ($afterCorrelationName === true || is_string ($afterCorrelationName)) {
                $tmpColumns = $this -> _parts[self::COLUMNS];
                $this -> _parts[self::COLUMNS] = array();
            } else {
                $tmpColumns = array();
            }
            // find the correlation name to insert after
            if (is_string ($afterCorrelationName)) {
                while ($tmpColumns) {
                    $this -> _parts[self::COLUMNS][] = $currentColumn = array_shift ($tmpColumns);
                    if ($currentColumn[0] == $afterCorrelationName) {
                        break;
                    }
                }
            }
            // apply current values to current stack
            foreach ($columnValues as $columnValue) {
                array_push ($this -> _parts[self::COLUMNS], $columnValue);
            }
            // finish ensuring that all previous values are applied (if they exist)
            while ($tmpColumns) {
                array_push ($this -> _parts[self::COLUMNS], array_shift ($tmpColumns));
            }
        }
    }

    /**
     * Internal function for creating the where clause
     * @param string $condition
     * @param mixed $value optional
     * @param string $type optional
     * @param boolean $bool true = AND, false = OR
     * @return string clause
     */
    protected function _where ($condition, $value = null, $type = null, $bool = true) {
        if (count ($this -> _parts[self::UNION])) {
            require_once 'Zend/Db/Select/Exception.php';
            throw new Zend_Db_Select_Exception ("Invalid use of where clause with " . self::SQL_UNION);
        }
        if ($value !== null) {
            $condition = $this -> _adapter -> quoteInto ($condition, $value, $type);
        }
        $cond = "";
        if ($this -> _parts[self::WHERE]) {
            if ($bool === true) {
                $cond = self::SQL_AND . ' ';
            } else {
                $cond = self::SQL_OR . ' ';
            }
        }
        return $cond . "($condition)";
    }

    /**
     *
     * @return array
     */
    protected function _getDummyTable () {
        return array();
    }

    /**
     * Return a quoted schema name
     * @param string $schema The schema name OPTIONAL
     * @return string null
     */
    protected function _getQuotedSchema ($schema = null) {
        if ($schema === null) {
            return null;
        }
        return $this -> _adapter -> quoteIdentifier ($schema, true) . '.';
    }

    /**
     * Return a quoted table name
     * @param string $tableName The table name
     * @param string $correlationName The correlation name OPTIONAL
     * @return string
     */
    protected function _getQuotedTable ($tableName, $correlationName = null) {
        return $this -> _adapter -> quoteTableAs ($tableName, $correlationName, true);
    }

    /**
     * Render DISTINCT clause
     * @param string $sql SQL query
     * @return string
     */
    protected function _renderDistinct ($sql) {
        if ($this -> _parts[self::DISTINCT]) {
            $sql .= ' ' . self::SQL_DISTINCT;
        }
        return $sql;
    }

    /**
     * Render DISTINCT clause
     * @param string $sql SQL query
     * @return string null
     */
    protected function _renderColumns ($sql) {
        if ( ! count ($this -> _parts[self::COLUMNS])) {
            return null;
        }
        $columns = array();
        foreach ($this -> _parts[self::COLUMNS] as $columnEntry) {
            list ($correlationName, $column, $alias) = $columnEntry;
            if ($column instanceof Zend_Db_Expr) {
                $columns[] = $this -> _adapter -> quoteColumnAs ($column, $alias, true);
            } else {
                if ($column == self::SQL_WILDCARD) {
                    $column = new Zend_Db_Expr (self::SQL_WILDCARD);
                    $alias = null;
                }
                if (empty ($correlationName)) {
                    $columns[] = $this -> _adapter -> quoteColumnAs ($column, $alias, true);
                } else {
                    $columns[] = $this -> _adapter -> quoteColumnAs (array($correlationName, $column), $alias, true);
                }
            }
        }
        return $sql .= ' ' . implode (', ', $columns);
    }

    /**
     * Render FROM clause
     * @param string $sql SQL query
     * @return string
     */
    protected function _renderFrom ($sql) {
        /*
         * If no table specified, use RDBMS-dependent solution for table-less query. e.g. DUAL in Oracle.
         */
        if (empty ($this -> _parts[self::FROM])) {
            $this -> _parts[self::FROM] = $this -> _getDummyTable ();
        }
        $from = array();
        foreach ($this -> _parts[self::FROM] as $correlationName => $table) {
            $tmp = '';
            $joinType = ($table['joinType'] == self::FROM) ? self::INNER_JOIN : $table['joinType'];
            // Add join clause (if applicable)
            if ( ! empty ($from)) {
                $tmp .= ' ' . strtoupper ($joinType) . ' ';
            }
            $tmp .= $this -> _getQuotedSchema ($table['schema']);
            $tmp .= $this -> _getQuotedTable ($table['tableName'], $correlationName);
            // Add join conditions (if applicable)
            if ( ! empty ($from) &&  ! empty ($table['joinCondition'])) {
                $tmp .= ' ' . self::SQL_ON . ' ' . $table['joinCondition'];
            }
            // Add the table name and condition add to the list
            $from[] = $tmp;
        }
        // Add the list of all joins
        if ( ! empty ($from)) {
            $sql .= ' ' . self::SQL_FROM . ' ' . implode ("\n", $from);
        }
        return $sql;
    }

    /**
     * Render UNION query
     * @param string $sql SQL query
     * @return string
     */
    protected function _renderUnion ($sql) {
        if ($this -> _parts[self::UNION]) {
            $parts = count ($this -> _parts[self::UNION]);
            foreach ($this -> _parts[self::UNION] as $cnt => $union) {
                list ($target, $type) = $union;
                if ($target instanceof Zend_Db_Select) {
                    $target = $target -> assemble ();
                }
                $sql .= $target;
                if ($cnt < $parts - 1) {
                    $sql .= ' ' . $type . ' ';
                }
            }
        }
        return $sql;
    }

    /**
     * Render WHERE clause
     * @param string $sql SQL query
     * @return string
     */
    protected function _renderWhere ($sql) {
        if ($this -> _parts[self::FROM] && $this -> _parts[self::WHERE]) {
            $sql .= ' ' . self::SQL_WHERE . ' ' . implode (' ', $this -> _parts[self::WHERE]);
        }
        return $sql;
    }

    /**
     * Render GROUP clause
     * @param string $sql SQL query
     * @return string
     */
    protected function _renderGroup ($sql) {
        if ($this -> _parts[self::FROM] && $this -> _parts[self::GROUP]) {
            $group = array();
            foreach ($this -> _parts[self::GROUP] as $term) {
                $group[] = $this -> _adapter -> quoteIdentifier ($term, true);
            }
            $sql .= ' ' . self::SQL_GROUP_BY . ' ' . implode (",\n\t", $group);
        }
        return $sql;
    }

    /**
     * Render HAVING clause
     * @param string $sql SQL query
     * @return string
     */
    protected function _renderHaving ($sql) {
        if ($this -> _parts[self::FROM] && $this -> _parts[self::HAVING]) {
            $sql .= ' ' . self::SQL_HAVING . ' ' . implode (' ', $this -> _parts[self::HAVING]);
        }
        return $sql;
    }

    /**
     * Render ORDER clause
     * @param string $sql SQL query
     * @return string
     */
    protected function _renderOrder ($sql) {
        if ($this -> _parts[self::ORDER]) {
            $order = array();
            foreach ($this -> _parts[self::ORDER] as $term) {
                if (is_array ($term)) {
                    if (is_numeric ($term[0]) && strval (intval ($term[0])) == $term[0]) {
                        $order[] = (int) trim ($term[0]) . ' ' . $term[1];
                    } else {
                        $order[] = $this -> _adapter -> quoteIdentifier ($term[0], true) . ' ' . $term[1];
                    }
                } else 
                    if (is_numeric ($term) && strval (intval ($term)) == $term) {
                        $order[] = (int) trim ($term);
                    } else {
                        $order[] = $this -> _adapter -> quoteIdentifier ($term, true);
                    }
            }
            $sql .= ' ' . self::SQL_ORDER_BY . ' ' . implode (', ', $order);
        }
        return $sql;
    }

    /**
     * Render LIMIT OFFSET clause
     * @param string $sql SQL query
     * @return string
     */
    protected function _renderLimitoffset ($sql) {
        $count = 0;
        $offset = 0;
        if ( ! empty ($this -> _parts[self::LIMIT_OFFSET])) {
            $offset = (int) $this -> _parts[self::LIMIT_OFFSET];
            $count = PHP_INT_MAX;
        }
        if ( ! empty ($this -> _parts[self::LIMIT_COUNT])) {
            $count = (int) $this -> _parts[self::LIMIT_COUNT];
        }
        /*
         * Add limits clause
         */
        if ($count > 0) {
            $sql = trim ($this -> _adapter -> limit ($sql, $count, $offset));
        }
        return $sql;
    }

    /**
     * Render FOR UPDATE clause
     * @param string $sql SQL query
     * @return string
     */
    protected function _renderForupdate ($sql) {
        if ($this -> _parts[self::FOR_UPDATE]) {
            $sql .= ' ' . self::SQL_FOR_UPDATE;
        }
        return $sql;
    }

    /**
     * Turn magic function calls into non-magic function calls
     * for joinUsing syntax
     * @param string $method
     * @param array $args OPTIONAL Zend_Db_Table_Select query modifier
     * @return Zend_Db_Select
     * @throws Zend_Db_Select_Exception If an invalid method is called.
     */
    public function __call ($method, array $args) {
        $matches = array();
        /**
         * Recognize methods for Has-Many cases:
         * findParent<Class>()
         * findParent<Class>By<Rule>()
         * Use the non-greedy pattern repeat modifier e.g.
         * \w+?
         */
        if (preg_match ('/^join([a-zA-Z]*?)Using$/', $method, $matches)) {
            $type = strtolower ($matches[1]);
            if ($type) {
                $type .= ' join';
                if ( ! in_array ($type, self::$_joinTypes)) {
                    require_once 'Zend/Db/Select/Exception.php';
                    throw new Zend_Db_Select_Exception ("Unrecognized method '$method()'");
                }
                if (in_array ($type, array(self::CROSS_JOIN, self::NATURAL_JOIN))) {
                    require_once 'Zend/Db/Select/Exception.php';
                    throw new Zend_Db_Select_Exception ("Cannot perform a joinUsing with method '$method()'");
                }
            } else {
                $type = self::INNER_JOIN;
            }
            array_unshift ($args, $type);
            return call_user_func_array (array($this, '_joinUsing'), $args);
        }
        require_once 'Zend/Db/Select/Exception.php';
        throw new Zend_Db_Select_Exception ("Unrecognized method '$method()'");
    }

    /**
     * Implements magic method.
     * @return string This object as a SELECT string.
     */
    public function __toString () {
        try {
            $sql = $this -> assemble ();
        } catch (Exception $e) {
            trigger_error ($e -> getMessage (), E_USER_WARNING);
            $sql = '';
        }
        return (string) $sql;
    }

}
